﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MyBet.Web.DAL;
using System.Web.Security;
using System.Web.Profile;
using System.ComponentModel.DataAnnotations;
using System.ServiceModel.DomainServices.Hosting;
using System.ServiceModel.DomainServices.Server;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using System.Data.SqlClient;

namespace MyBet.Web
{
    [DataContractAttribute(IsReference = true)]
    [Serializable()]
    public class RankObject
    {

        private string _friendlyName;
        private int _customContestId;
        private long _totalScore;
        private int _score;
        private int _goodPredictionCount;
        private int _goodScoreCount;
        private int _eventCount;
        private int _matchCount;
        private int _rank;
        private Guid _userId;
        private string _userName;
        private int _rankDifferential;
        private Double _averageScore;
        private int _bestOfEventCount;

        [Key()]
        [DataMemberAttribute()]
        public Guid UserId
        {
            get { return _userId; }
            set { _userId = value; }
        }

        [DataMemberAttribute()]
        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        [DataMemberAttribute()]
        public string FriendlyName
        {
            get { return _friendlyName; }
            set { _friendlyName = value; }
        }

        [DataMemberAttribute()]
        public int Rank
        {
            get { return _rank; }
            set { _rank = value; }
        }

        [DataMemberAttribute()]
        public int CustomContestId
        {
            get { return _customContestId; }
            set { _customContestId = value; }
        }

        [DataMemberAttribute()]
        public long TotalScore
        {
            get { return _totalScore; }
            set { _totalScore = value; }
        }

        [DataMemberAttribute()]
        public int Score
        {
            get { return _score; }
            set { _score = value; }
        }

        [DataMemberAttribute()]
        public int GoodPredictionCount
        {
            get { return _goodPredictionCount; }
            set { _goodPredictionCount = value; }
        }

        [DataMemberAttribute()]
        public int GoodScoreCount
        {
            get { return _goodScoreCount; }
            set { _goodScoreCount = value; }
        }

        [DataMemberAttribute()]
        public int EventCount
        {
            get { return _eventCount; }
            set { _eventCount = value; }
        }

        [DataMemberAttribute()]
        public int MatchCount
        {
            get { return _matchCount; }
            set { _matchCount = value; }
        }

        [DataMemberAttribute()]
        public int RankDifferential
        {
            get { return _rankDifferential; }
            set { _rankDifferential = value; }
        }

        [DataMemberAttribute()]
        public Double AverageScore
        {
            get { return _averageScore; }
            set { _averageScore = value; }
        }

        [DataMemberAttribute()]
        public int BestOfEventCount
        {
            get { return _bestOfEventCount; }
            set { _bestOfEventCount = value; }
        }

        ///// <summary>
        ///// Create object from database information
        ///// </summary>
        ///// <param name="m"></param>
        ///// <returns></returns>
        //public static RankObject CopyFrom(Ranks rnk)
        //{
        //    RankObject rankTgt = new RankObject();
        //    rankTgt.CustomContestId = rnk.CustomContestId;
        //    rankTgt.TotalScore = rnk.TotalScore;
        //    rankTgt.GoodPredictionCount = rnk.GoodPredictionCount;
        //    rankTgt.GoodScoreCount = rnk.GoodScoreCount;
        //    rankTgt.EventCount = rnk.EventCount;
        //    rankTgt.MatchCount = rnk.MatchCount;
        //    rankTgt.UserId = rnk.UserId;
        //    rankTgt.UserName = rnk.UserName;
        //    return rankTgt;
        //}

        /// <summary>
        /// Get user ranks of a given contest
        /// </summary>
        /// <param name="dbContext">Database connection</param>
        /// <param name="customContestId">Contest identifier</param>
        /// <returns></returns>
        public static List<RankObject> GetRanksInDatabase(MyBetDBEntities dbContext, int customContestId)
        {
            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId }
                        };

            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select rnk.*, s.SubscriptionId, au.UserName, au.UserId,
                (SELECT SUM(EventScores.IsBestScore) 
                    FROM EventScores
                    WHERE EventScores.UserId = s.UserId) as BestOfEventCount
                    from Ranks rnk 
                   left join Subscriptions s on s.SubscriptionId=rnk.SubscriptionId
                   left join aspnet_Users au on au.UserId = s.UserId
                   where s.CustomContestId=@p0 ORDER BY rnk.TotalScore DESC, rnk.GoodPredictionCount DESC, au.UserName"), parameters);

            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }

        /// <summary>
        /// Get user ranks of a given contest
        /// </summary>
        /// <param name="dbContext">Database connection</param>
        /// <param name="contestId">Contest identifier</param>
        /// <returns></returns>
        public static List<RankObject> GetAverageRanksInDatabase(MyBetDBEntities dbContext, int customContestId)
        {
            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId }
                        };

            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select au.UserName,au.UserId,  AVG(cast(evtScores.Score as Float)) as AverageScore 
                                ,rnk.EventCount
                                from [ParieurProDB].[dbo].[EventScores] evtScores
                                left join [ParieurProDB].[dbo].[aspnet_Users] au on au.UserId = evtScores.UserId
                                left join [ParieurProDB].[dbo].[Events] evt on evt.EventId = evtScores.EventId
                                left join [ParieurProDB].[dbo].[Subscriptions] sub on sub.UserId = evtScores.UserId
                                left join [ParieurProDB].[dbo].[Ranks] rnk on rnk.SubscriptionId = sub.SubscriptionId
                                left join [ParieurProDB].[dbo].[CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                where evt.ContestId=ccl.ContestId and sub.CustomContestId=@p0
                                and evtScores.EventId in (
                                SELECT EventId from [ParieurProDB].[dbo].[Matchs] m
                                left join [ParieurProDB].[dbo].[UserBets] ub on ub.MatchId = m.MatchId
                                where ub.UserId = evtScores.UserId
                                )
                                group by au.UserName,au.UserId ,rnk.EventCount
                                ORDER BY AverageScore DESC, au.UserName;"), parameters);

            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }

                /// <summary>
        /// Get user ranks of a given contest
        /// </summary>
        /// <param name="dbContext">Database connection</param>
        /// <param name="contestId">Contest identifier</param>
        /// <returns></returns>
        public static List<RankObject> GetRanksInDatabaseFromEvent(MyBetDBEntities dbContext, int customContestId, int eventId)
        {
            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId },
                           new SqlParameter { ParameterName = "p1", Value = eventId }
                        };
            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select au.UserName, au.UserId, evtScores.Score, evtScores.GoodPredictionCount, evtScores.GoodScoreCount from [ParieurProDB].[dbo].[EventScores] evtScores
                                 left join [ParieurProDB].[dbo].[aspnet_Users] au on au.UserId = evtScores.UserId
                                 left join [ParieurProDB].[dbo].[Events] evt on evt.EventId = evtScores.EventId
                                 left join [ParieurProDB].[dbo].[CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                 where evt.ContestId=ccl.ContestId and evtScores.EventId=@p1 ORDER BY evtScores.Score DESC, au.UserName;"), parameters);

            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }
        

        /// <summary>
        /// Get events with a rank for a given contest
        /// </summary>
        /// <param name="dbContext">Database connection</param>
        /// <param name="contestId">Contest identifier</param>
        /// <returns></returns>
        public static List<EventObject> GetEventsInDatabase(MyBetDBEntities dbContext, int contestId)
        {
            IEnumerable<EventObject> eventsResult = dbContext.ExecuteStoreQuery<EventObject>
                (String.Format(@"SELECT distinct evtScores.[EventId], evt.[DayNumber]
                                 FROM [ParieurProDB].[dbo].[EventScores] evtScores
                                 LEFT JOIN [ParieurProDB].[dbo].[Events] evt on evt.[EventId]=evtScores.[EventId]
                                 WHERE evt.ContestId={0} and evt.CloseDate<SYSUTCDATETIME() ORDER BY evt.[DayNumber] DESC;", contestId));

            List<EventObject> contestEvents = eventsResult.ToList();
            return contestEvents;
        }

        public static bool AddOrUpdate(MyBetDBEntities dbContext, Ranks rank)
        {
            MyBetDBEntities db = dbContext;
            try
            {
                var query = (from rnk in db.Ranks
                             where rnk.SubscriptionId == rank.SubscriptionId
                             select rnk);
                IList<Ranks> existingRank = query.ToList();

                if (existingRank.Count < 1)
                {
                    // Add
                    db.Ranks.AddObject(rank);
                    //dbContext.SaveChanges();
                    return true;
                }
                else
                {
                    // Update
                    existingRank[0].EventCount = rank.EventCount;
                    existingRank[0].GoodPredictionCount = rank.GoodPredictionCount;
                    existingRank[0].GoodScoreCount = rank.GoodScoreCount;
                    existingRank[0].MatchCount = rank.MatchCount;
                    existingRank[0].SubscriptionId = rank.SubscriptionId;
                    existingRank[0].TotalScore = rank.TotalScore;
                    existingRank[0].CurrentRank = rank.CurrentRank;
                    existingRank[0].RankDifferential = rank.RankDifferential;
                    //dbContext.SaveChanges();
                    return true;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }

        public static List<EventObject> GetEventList(int contestId)
        {
            List<EventObject> eventList = new List<EventObject>();
            using (MyBetDBEntities dbContext = new MyBetDBEntities())
            {
                eventList = RankObject.GetEventsInDatabase(dbContext, contestId);
            }
            return eventList;
        }


        public static List<RankObject> GetContestRanksFromEvent(int contestId, int eventId)
        {
            List<RankObject> rankList = new List<RankObject>();
            List<RankObject> rankViewList;
            int rank = 1;

            using (MyBetDBEntities dbContext = new MyBetDBEntities())
            {
                if (eventId == 0)
                {
                    // Overall
                    rankViewList = RankObject.GetRanksInDatabase(dbContext, contestId);
                }
                else if (eventId == -1)
                {
                    // Average
                    rankViewList = RankObject.GetAverageRanksInDatabase(dbContext, contestId);
                }
                else
                {
                    // Event rank
                    rankViewList = RankObject.GetRanksInDatabaseFromEvent(dbContext, contestId, eventId);
                }
            }

            foreach (RankObject rnk in rankViewList)
            {
                rnk.Rank = rank;
                if (rnk.Score != 0)
                {
                    rnk.TotalScore = rnk.Score;
                }
                if (rnk.EventCount == 0)
                {
                    rnk.EventCount = 1;
                }
                rankList.Add(rnk);
                rank++;
            }

            return rankList;
        }

    }
}